package com.dy.yunying.biz.dao.hbdataclickhouse.impl;

import com.dy.yunying.api.req.hongbao.HbActiveDataReq;
import com.dy.yunying.api.vo.hbdata.*;
import com.dy.yunying.biz.config.YunYingProperties;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringEscapeUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.List;

/**
 * @author ：lile
 * @date ：2021/11/17 18:16
 * @description：
 * @modified By：
 */
@Slf4j
@Component
public class HbActiveDataDao {

	@Autowired
	private YunYingProperties yunYingProperties;

	@Resource(name = "hbdataclickDcSessionTemplate")
	private JdbcTemplate clickhouseTemplate;

	/**
	 * 弹窗公告展示，消息推送
	 *
	 * @return
	 */
	public List<HbDataNoticeVo> popupList(HbActiveDataReq req) {
		StringBuilder sql = new StringBuilder();
		sql.append("  SELECT  \n");
		sql.append("  sourceName sourceName, \n");
		sql.append("  popupType popupType, \n");
		sql.append("  SUM(serviceNum) serviceNum, \n");
		sql.append("  SUM(servicePV) servicePV, \n");
		sql.append("  SUM(serviceUV) serviceUV \n");
		sql.append("  FROM( \n");
		sql.append("  SELECT \n");
		sql.append("  IF( ( source_name IS NOT NULL AND source_name != '' ), source_name, b ) sourceName, \n");
		sql.append("  IF( popup_type > 0, popup_type, e ) popupType, \n");
		sql.append("  if(dx_event_name = 'data_arrive',coalesce(count(*),0),0 ) serviceNum, \n");
		sql.append("  if(dx_event_name = 'page_view',coalesce(count(*),0),0 ) servicePV,\n");
		sql.append("  if(dx_event_name = 'module_click',coalesce(count(DISTINCT roleid),0),0 ) serviceUV\n");
		sql.append("  from\n");
		sql.append(yunYingProperties.getHbdxtable()).append("\n");
		sql.append("  where \n");
		sql.append("  dx_event_name in ('data_arrive', 'page_view', 'module_click')\n");
		sql.append("  and place in ('notice','float_tip')\n");
		sql.append("  and (source_id > 0 OR c > 0)\n");
		sql.append(this.selectPopupCondSB(req));
		sql.append(" group by sourceName,dx_event_name,popupType,place )\n");
		sql.append(" group by sourceName,popupType \n");
		log.info("sql : [{}]", sql.toString());
		List<HbDataNoticeVo> list = clickhouseTemplate.query(sql.toString(), new Object[]{},
				new BeanPropertyRowMapper<>(HbDataNoticeVo.class));
		return list;
	}

	public String selectPopupCondSB(HbActiveDataReq req) {
		StringBuffer commCondSB = new StringBuffer();
		if (StringUtils.isNotBlank(req.getActiveName())) {
			commCondSB.append(" and ");
			commCondSB.append("(sourceName like '%").append(req.getActiveName()).append("%'");
			commCondSB.append(" or activity_name like '%").append(req.getActiveName()).append("%'");
			commCondSB.append(" )");
		}
		return commCondSB.toString();
	}

	/**
	 * SDK浮标特效点击数
	 *
	 * @param req
	 * @return
	 */
	public List<HbFloatClickVo> floatClick(HbActiveDataReq req) {
		StringBuilder sql = new StringBuilder();
		sql.append("  SELECT  \n");
		sql.append("  IF(source_id > 0, source_id, c )  activityId,  \n");
		sql.append(" IF( ( source_name IS NOT NULL AND source_name != '' ), source_name, b ) activityName, \n");
		sql.append("float_content  floatContent,\n");
		sql.append("coalesce(count(*),0) clickNum \n");
		sql.append("from \n");
		sql.append(yunYingProperties.getHbdxtable()).append(" \n");
		sql.append("where \n");
		sql.append("dx_event_name = 'module_click' \n");
		sql.append("and place = 'float_tip' \n");

		sql.append(this.getCondition(req));

		sql.append(" group by activityId,activityName,floatContent  \n");
		sql.append("  order by activityId desc  \n");
		log.info("sql : [{}]", sql.toString());
		List<HbFloatClickVo> list = clickhouseTemplate.query(sql.toString(), new Object[]{},
				new BeanPropertyRowMapper<>(HbFloatClickVo.class));
		return list;
	}


	/**
	 * 任务模块页PV/UV
	 *
	 * @param req
	 * @return
	 */
	public List<HbActiveTypePuVo> activeTypePU(HbActiveDataReq req) {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT  \n");
		sql.append("activity_type activityType, \n");
		sql.append("activity_name activityName, \n");
		sql.append("coalesce(count(*),0) servicePV, \n");
		sql.append("coalesce(count(DISTINCT roleid),0) serviceUV \n");
		sql.append("from \n");
		sql.append(yunYingProperties.getHbdxtable()).append(" \n");
		sql.append("where \n");
		sql.append("dx_event_name = 'page_view' \n");
		sql.append(this.selectComCondSB(req));
		sql.append("and place in('rank_index','recharge_index','invite_index') \n");
		sql.append("group by activity_type,activity_name \n");
		log.info("sql : [{}]", sql.toString());
		List<HbActiveTypePuVo> list = clickhouseTemplate.query(sql.toString(), new Object[]{},
				new BeanPropertyRowMapper<>(HbActiveTypePuVo.class));
		return list;
	}

	/**
	 * 点击一键提现按钮数，点击提现记录按钮数 缺少activity_name字段
	 *
	 * @param req
	 * @return
	 */
	public List<HbActiveCashVo> activeCash(HbActiveDataReq req) {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT  \n");
		sql.append(" activity_name activityName, \n");
//		sql.append(" activity_type activityType, \n");
		sql.append(" SUM(oneClickNum)  oneClickNum, \n");
		sql.append(" SUM(cashClickNum)  cashClickNum \n");
		sql.append("from \n");
		sql.append("( \n");
		sql.append("SELECT \n");
		sql.append("activity_name, \n");
//		sql.append("activity_type, \n");
		sql.append("if(place = 'withdrawal',coalesce(count(*),0),0 ) oneClickNum, \n");
		sql.append("if(place = 'withdrawal_record',coalesce(count(*),0),0 ) cashClickNum \n");
		sql.append("from \n");
		sql.append(yunYingProperties.getHbdxtable()).append(" \n");
		sql.append("where \n");
		sql.append("dx_event_name = 'module_click' \n");
		sql.append("and place in('withdrawal','withdrawal_record') \n");
		sql.append(this.selectComCondSB(req));
		sql.append("group by place,activity_name \n");
		sql.append(") \n");
		sql.append("group by activity_name \n");
		log.info("sql : [{}]", sql.toString());
		List<HbActiveCashVo> list = clickhouseTemplate.query(sql.toString(), new Object[]{},
				new BeanPropertyRowMapper<>(HbActiveCashVo.class));
		return list;
	}

	/**
	 * 活动规则页
	 *
	 * @param req
	 * @return
	 */
	public List<HbActiveRuleVo> activeRule(HbActiveDataReq req) {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT  \n");
		sql.append(" activity_name activityName,  \n");
		sql.append(" coalesce(count(*),0) servicePV,  \n");
		sql.append(" coalesce(count(DISTINCT roleid),0) serviceUV  \n");
		sql.append("from \n");
		sql.append(yunYingProperties.getHbdxtable()).append(" \n");
		sql.append("where dx_event_name = 'page_view'\n");
		sql.append("and place = 'activity_rules' \n");
		sql.append(this.selectComCondSB(req));
		sql.append("group  by activity_name \n");
		log.info("sql : [{}]", sql.toString());
		List<HbActiveRuleVo> list = clickhouseTemplate.query(sql.toString(), new Object[]{},
				new BeanPropertyRowMapper<>(HbActiveRuleVo.class));
		return list;
	}

	/**
	 * 活动任务完成数
	 *
	 * @param req
	 * @return
	 */
	public List<HbActiveFinishVo> activeFinish(HbActiveDataReq req) {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT  \n");
		sql.append(" activity_name activityName,  \n");
		sql.append(" redpack_name hbName,  \n");
		sql.append(" coalesce(count(*),0) finishNum  \n");
		sql.append("from \n");
		sql.append(yunYingProperties.getHbdxtable()).append(" \n");
		sql.append("where dx_event_name = 'red_packet_task_done'\n");
		sql.append(this.selectComCondSB(req));
		sql.append("group  by activity_name,redpack_name \n");
		log.info("sql : [{}]", sql.toString());
		List<HbActiveFinishVo> list = clickhouseTemplate.query(sql.toString(), new Object[]{},
				new BeanPropertyRowMapper<>(HbActiveFinishVo.class));
		return list;
	}

	/**
	 * 各个档位游戏货币兑换数，各个档位现金提现数  (还未上报数据)
	 *
	 * @param req
	 * @return
	 */
	public List<HbActiveCashConfigVo> activeCashConfig(HbActiveDataReq req) {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT  \n");
		sql.append(" activity_id activityId,  \n");
		sql.append(" activity_name activityName,\n");
		sql.append(" cash_type cashType,\n");
		sql.append(" level_id cashConfig,\n");
		sql.append(" level_name cashConfigName,\n");
		sql.append(" SUM(money) moneySum,  \n");
		sql.append(" coalesce(count(*),0) cashNum \n");
		sql.append(" from \n");
		sql.append(yunYingProperties.getHbdxtable()).append(" \n");
		sql.append(" where \n");
		sql.append(" dx_event_name = 'withdraw_success' \n");

		sql.append(this.getCondition(req));

		sql.append(" group by activityId,activityName,cashType,cashConfig,cashConfigName \n");
		sql.append(" order by activityId desc \n");
		log.info("sql : [{}]", sql.toString());
		List<HbActiveCashConfigVo> list = clickhouseTemplate.query(sql.toString(), new Object[]{},
				new BeanPropertyRowMapper<>(HbActiveCashConfigVo.class));
		return list;
	}

	/**
	 * 点击领取奖励数，代金券领取数
	 *
	 * @param req
	 * @return
	 */
	public List<HbActiveHbDrawVo> activeHbDraw(HbActiveDataReq req) {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT \n");
		sql.append(" activity_id AS activityId, \n");
		sql.append(" redpack_id AS redpackId, \n");
		sql.append(" activity_name activityName, \n");
		sql.append(" redpack_name redpackName, \n");
		sql.append(" SUM(clickNum)  clickNum, \n");
		sql.append(" SUM(getNum) getNum \n");
		sql.append(" FROM \n");
		sql.append(" ( \n");
		sql.append(" SELECT  \n");
		sql.append(" activity_id, \n");
		sql.append(" if(dx_event_name = 'module_click', biz_id, redpack_id) AS redpack_id, \n");
		sql.append(" activity_name,  \n");
		sql.append(" redpack_name,  \n");
		sql.append(" if(dx_event_name = 'module_click', coalesce(count(*), 0), 0) clickNum,  \n");
		sql.append(" if(dx_event_name = 'red_packet_received', coalesce(count(*), 0), 0) getNum  \n");
		sql.append(" from  \n");
		sql.append(yunYingProperties.getHbdxtable()).append(" \n");
		sql.append(" where  \n");
		sql.append(" ((dx_event_name = 'module_click' and place = 'red_packet_click') or dx_event_name = 'red_packet_received') \n");
		sql.append(this.selectComCondSB(req));
		sql.append(" group by activity_id, redpack_id, activity_name, redpack_name, dx_event_name)  \n");
		sql.append(" group by activity_id, redpack_id, activity_name,redpack_name \n");
		log.info("sql : [{}]", sql.toString());
		List<HbActiveHbDrawVo> list = clickhouseTemplate.query(sql.toString(), new Object[]{},
				new BeanPropertyRowMapper<>(HbActiveHbDrawVo.class));
		return list;
	}

	/**
	 * SDK侧边栏红包UI点击数，收益明细按钮点击数  （未上报）
	 *
	 * @param req
	 * @return
	 */
	public List<HbActiveHbProfitVo> activeHbProfit(HbActiveDataReq req) {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT \n");
		sql.append(" SUM(hbClickNum) hbClickNum, \n");
		sql.append(" SUM(floatClickNum) floatClickNum, \n");
		sql.append(" SUM(earnClickNum) earnClickNum\n");
		sql.append(" FROM \n");
		sql.append(" ( \n");
		sql.append(" SELECT  \n");
		sql.append(" if(place='menu' and menu_code in ('10003','11003'),coalesce(count(*),0),0 ) hbClickNum, \n");
		sql.append(" if(place='float_tip',coalesce(count(*),0),0 ) floatClickNum,  \n");
		sql.append(" if(place='earnings',coalesce(count(*),0),0 ) earnClickNum  \n");
		sql.append(" from  \n");
		sql.append(yunYingProperties.getHbdxtable()).append(" \n");
		sql.append(" where  \n");
		sql.append(" dx_event_name = 'module_click'  \n");
		sql.append(" and place in('menu','earnings','float_tip')  \n");
//		sql.append(this.selectComCondSB(req));
		sql.append(" group by menu_code,place)  \n");
		log.info("sql : [{}]", sql.toString());
		List<HbActiveHbProfitVo> list = clickhouseTemplate.query(sql.toString(), new Object[]{},
				new BeanPropertyRowMapper<>(HbActiveHbProfitVo.class));
		return list;
	}


	/**
	 * 消息推送、PV、UV
	 *
	 * @param req
	 * @return
	 */
	public List<HbMessageVo> messageList(HbActiveDataReq req) {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT \n  ");
		sql.append(" activityId, \n  ");
		sql.append(" triggerName triggerName, \n  ");
		sql.append(" activityName activityName, \n  ");
		sql.append(" floatContent floatContent, \n  ");
		sql.append(" SUM(pushNum)  pushNum, \n  ");
		sql.append(" SUM(servicePV) servicePV,  \n  ");
		sql.append(" SUM(serviceUV) serviceUV \n  ");

		sql.append("  FROM  \n  ");
		sql.append("  ( \n  ");

		sql.append(" SELECT \n  ");
		sql.append(" activityId, \n  ");
		sql.append(" a.push_event_name triggerName, \n  ");
		sql.append(" activityName, \n  ");
		sql.append(" a.push_data_content floatContent, \n  ");
		sql.append(" a.pushNum pushNum, \n  ");
		sql.append(" b.servicePV servicePV, \n  ");
		sql.append(" b.serviceUV serviceUV \n  ");
		sql.append(" FROM \n  ");
		sql.append(" ( \n  ");
		sql.append(" SELECT\n  ");
		sql.append(" source_id activityId, \n  ");
		sql.append(" push_data_id,\n  ");
		sql.append(" push_event_name, \n  ");
		sql.append(" source_name activityName,  \n  ");
		sql.append(" push_data_content, \n  ");
		sql.append(" coalesce(count(*),0) pushNum \n  ");
		sql.append(" from \n  ");
		sql.append(yunYingProperties.getHbdxtable()).append(" \n");
		sql.append(" where \n  ");
		sql.append(" dx_event_name  = 'data_push' \n  ");

		sql.append(this.getCondition(req));

		sql.append(" group by  \n  ");
		sql.append(" source_id,  \n  ");
		sql.append(" push_data_id,  \n  ");
		sql.append(" push_event_name,\n  ");
		sql.append(" source_name,  \n  ");
		sql.append(" push_data_content \n  ");
		sql.append(" )a \n  ");
		sql.append(" FULL join  \n  ");
		sql.append(" (\n  ");
		sql.append(" SELECT \n  ");
		sql.append(" biz_id,  \n  ");
		sql.append(" SUM(servicePV) servicePV, \n  ");
		sql.append(" SUM(serviceUV) serviceUV \n  ");
		sql.append(" FROM \n  ");
		sql.append(" (  \n  ");
		sql.append("  SELECT  \n  ");
		sql.append("  biz_id,   \n  ");
		sql.append(" if(dx_event_name = 'page_view' and place ='message_tip',coalesce(count(*),0),0 ) servicePV, \n  ");
		sql.append(" if(dx_event_name = 'module_click' and place ='message_tip',coalesce(count(DISTINCT roleid),0),0 ) serviceUV \n  ");
		sql.append(" from \n  ");
		sql.append(yunYingProperties.getHbdxtable());
		sql.append(" GLOBAL right join (   SELECT    push_data_id    from   ");
		sql.append(yunYingProperties.getHbdxtable());
		sql.append(" where    dx_event_name  = 'data_push' group by push_data_id) biz on biz_id = biz.push_data_id " ).append(" \n");
		sql.append(" where \n  ");
		sql.append(" dx_event_name in('page_view', 'module_click') \n  ");
		sql.append(" and place in ('message_tip') \n  ");

		if (StringUtils.isNotBlank(req.getStartTime())) {
			sql.append(" and dx_part_date >= '").append(req.getStartTime()).append("'");
		}
		if (StringUtils.isNotBlank(req.getEndTime())) {
			sql.append(" and dx_part_date <= '").append(req.getEndTime()).append("'");
		}

//		sql.append(" and biz_id in ( \n  ");
//		sql.append(" SELECT \n  ");
//		sql.append(" push_data_id \n  ");
//		sql.append(" from \n  ");
//		sql.append(yunYingProperties.getHbdxtable()).append(" \n");
//		sql.append(" where \n  ");
//		sql.append(" dx_event_name  = 'data_push' group by push_data_id) \n  ");

		sql.append(" GROUP BY \n  ");
		sql.append(" dx_event_name, \n  ");
		sql.append(" biz_id, \n  ");
		sql.append(" place \n  ");
		sql.append(" ) \n  ");
		sql.append(" group by biz_id \n  ");
		sql.append(" )b  \n  ");
		sql.append(" on  b.biz_id = toInt64(a.push_data_id) \n  ");
		sql.append("   )  \n  ");
		sql.append("   where 1=1   \n  ");
		sql.append("   and activityId != 0   \n  ");
		sql.append("  group by   \n  ");
		sql.append("  activityId,   \n  ");
		sql.append("  triggerName,  \n  ");
		sql.append("  activityName,  \n  ");
		sql.append("  floatContent  \n  ");
		sql.append("  order by activityId desc  \n  ");

		log.info("sql : [{}]", sql.toString());
		List<HbMessageVo> list = clickhouseTemplate.query(sql.toString(), new Object[]{},
				new BeanPropertyRowMapper<>(HbMessageVo.class));
		return list;
	}

	/**
	 * 邀请弹框展示数
	 *
	 * @param req
	 * @return
	 */
	public List<HbInvitePvVo> invitePvList(HbActiveDataReq req) {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT  \n");
		sql.append(" biz_id activityId,  \n");
		sql.append(" activity_name activityName, \n");
		sql.append(" coalesce(count(*),0)  servicePV, \n");
		sql.append("  coalesce(count(DISTINCT roleid),0) serviceUV \n");
		sql.append(" from \n");
		sql.append(yunYingProperties.getHbdxtable()).append(" \n");
		sql.append(" where \n");
		sql.append(" dx_event_name = 'page_view' \n");
		sql.append(" and place in ('invite') \n");

		sql.append(this.getCondition(req));

		sql.append(" group by activityId, activityName  \n");
		sql.append("  order by activityId desc  \n");
		log.info("sql : [{}]", sql.toString());
		List<HbInvitePvVo> list = clickhouseTemplate.query(sql.toString(), new Object[]{},
				new BeanPropertyRowMapper<>(HbInvitePvVo.class));
		return list;
	}

	/**
	 * 受邀页面
	 *
	 * @param req
	 * @return
	 */
	public List<HbInvitedVo> invitedList(HbActiveDataReq req) {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT  \n");
		sql.append(" activity_name sourceName,  \n");
		sql.append(" SUM(servicePV) servicePV,  \n");
		sql.append(" SUM(serviceUV) serviceUV  \n");
		sql.append(" FROM  \n");
		sql.append(" (  \n");
		sql.append(" SELECT  \n");
		sql.append(" activity_name,  \n");
		sql.append(" if(dx_event_name = 'page_view',coalesce(count(*),0),0 ) servicePV,  \n");
		sql.append(" if(dx_event_name = 'module_click',coalesce(count(DISTINCT roleid),0),0 ) serviceUV  \n");
		sql.append(" from  \n");
		sql.append(yunYingProperties.getHbdxtable()).append(" \n");
		sql.append(" where  \n");
		sql.append(" dx_event_name in('page_view','module_click')  \n");
		sql.append(" and place in ('be_invited','be_invited_click')  \n");
		sql.append(this.selectComCondSB(req));
		sql.append(" group by activity_name,dx_event_name,place  \n");
		sql.append(")  \n");
		sql.append("group by activity_name \n");
		log.info("sql : [{}]", sql.toString());
		List<HbInvitedVo> list = clickhouseTemplate.query(sql.toString(), new Object[]{},
				new BeanPropertyRowMapper<>(HbInvitedVo.class));
		return list;
	}

	/**
	 * 公告入参 查询条件
	 *
	 * @param req
	 * @return like ‘%sn110%’
	 */
	public String selectComCondSB(HbActiveDataReq req) {
		StringBuffer commCondSB = new StringBuffer();
		if (StringUtils.isNotBlank(req.getActiveName())) {
			commCondSB.append(" and ");
			commCondSB.append("(source_name like '%").append(req.getActiveName()).append("%'");
			commCondSB.append(" or activity_name like '%").append(req.getActiveName()).append("%'");
			commCondSB.append(" )");
		}
		return commCondSB.toString();
	}

	private String getCondition(HbActiveDataReq req) {
		StringBuffer sql = new StringBuffer();
		if (StringUtils.isNotBlank(req.getActiveName())) {
			sql.append("and activityName like '%").append(StringEscapeUtils.escapeSql(req.getActiveName())).append("%'");
		}
		if (StringUtils.isNotBlank(req.getActiveId())) {
			sql.append(" and activityId = ").append(req.getActiveId());
		}
		if (StringUtils.isNotBlank(req.getStartTime())) {
			sql.append(" and dx_part_date >= '").append(req.getStartTime()).append("'");
		}
		if (StringUtils.isNotBlank(req.getEndTime())) {
			sql.append(" and dx_part_date <= '").append(req.getEndTime()).append("'");
		}
		return sql.toString();
	}


	/**
	 * 点击领取奖励数，代金券领取数
	 *
	 * @param req
	 * @return
	 */
	public List<HbActiveHbDrawVo> activityTaskDetail(HbActiveDataReq req) {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT \n");
		sql.append(" activityId, \n");
		sql.append(" redpackId, \n");
		sql.append(" activityName, \n");
		sql.append(" redpackName, \n");
		sql.append(" SUM(clickNum)  clickNum, \n");
		sql.append(" SUM(getNum) getNum, \n");
		sql.append(" SUM(finishNum) finishNum \n");
		sql.append(" FROM \n");
		sql.append(" ( \n");
		sql.append(" SELECT  \n");
		sql.append(" activity_id activityId, \n");
		sql.append(" if(dx_event_name = 'module_click', biz_id, redpack_id) AS redpackId, \n");
		sql.append(" activity_name activityName,  \n");
		sql.append(" redpack_name redpackName,  \n");
		sql.append(" if(dx_event_name = 'module_click', coalesce(count(*), 0), 0) clickNum,  \n");
		sql.append(" if(dx_event_name = 'red_packet_received', coalesce(count(*), 0), 0) getNum,  \n");
		sql.append(" if(dx_event_name = 'red_packet_task_done', coalesce(count(*), 0), 0) finishNum    \n");
		sql.append(" from  \n");
		sql.append(yunYingProperties.getHbdxtable()).append(" \n");
		sql.append(" where  \n");
		sql.append(" ((dx_event_name = 'module_click' and place = 'red_packet_click') or dx_event_name = 'red_packet_received' or dx_event_name = 'red_packet_task_done') \n");

		sql.append(this.getCondition(req));

		sql.append(" group by activityId, redpackId, activityName, redpackName, dx_event_name)  \n");
		sql.append(" group by activityId, redpackId, activityName,redpackName  \n");
		sql.append(" order by activityId desc");
		log.info("sql : [{}]", sql.toString());
		List<HbActiveHbDrawVo> list = clickhouseTemplate.query(sql.toString(), new Object[]{},
				new BeanPropertyRowMapper<>(HbActiveHbDrawVo.class));
		return list;
	}

	/**
	 * 活动访问和参与概况
	 *
	 * @param req
	 * @return
	 */
	public List<HbActiveVisitDetailVo> activityVisitDetail(HbActiveDataReq req) {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT ").append(" \n");
		sql.append(" 	 activityId, ").append(" \n");
		sql.append(" 	 activityName,").append(" \n");
		sql.append(" 	 activityPV,").append(" \n");
		sql.append(" 	 activityUV, ").append(" \n");
		sql.append(" 	 popUpNum, ").append(" \n");
		sql.append(" 	 popUpPV, ").append(" \n");
		sql.append(" 	 popUpUV, ").append(" \n");
		sql.append(" 	 oneWithdrawalClickNum,").append(" \n");
		sql.append(" 	 withdrawalRecordClickNum,").append(" \n");
		sql.append(" 	 withdrawalClickNum,").append(" \n");
		sql.append(" 	 floatClickNum,").append(" \n");
		sql.append(" 	 earnClickNum  ").append(" \n");
		sql.append("  FROM (").append(" \n");
		sql.append("   SELECT ").append(" \n");
		sql.append(" 	 activityId, ").append(" \n");
		sql.append(" 	 activityName,   ").append(" \n");
		sql.append(" 	 activityPV,  ").append(" \n");
		sql.append(" 	 activityUV, ").append(" \n");
		sql.append(" 	 popUpNum,").append(" \n");
		sql.append(" 	 popUpPV, ").append(" \n");
		sql.append(" 	 popUpUV  ").append(" \n");
		sql.append("  FROM ( ").append(" \n");
		sql.append(" 	 SELECT  ").append(" \n");
		sql.append(" 		biz_id activityId, ").append(" \n");
		sql.append(" 		activity_name activityName,").append(" \n");
		sql.append(" 		coalesce(count(*),0) activityPV, ").append(" \n");
		sql.append(" 		coalesce(count(DISTINCT roleid),0) activityUV ").append(" \n");
		sql.append(" 	 from  ").append(yunYingProperties.getHbdxtable() + " \n");
		sql.append(" 	 where dx_event_name = 'page_view'  ").append(" \n");

		sql.append(this.getCondition(req));
		// 上报活动详情展示数据有误，暂时按位置和活动类型完全匹配
		sql.append(" 	 and ( (place='rank_index' and activity_type=1) or (place='recharge_index' and activity_type=2) or (place='invite_index' and activity_type=3) or (place='custom_index' and activity_type=4))      ").append(" \n");
		sql.append(" 	 and activityId != 0  ").append(" \n");
		sql.append(" 	 and activityName != '' ").append(" \n");
		sql.append(" 	 group by activityId,activityName ").append(" \n");
		sql.append(" 	 order by activityId desc ").append(" \n");
		sql.append("  ) a   ").append(" \n");
		sql.append("                                ").append(" \n");
		sql.append("  FULL JOIN (                   ").append(" \n");
		sql.append(" 	 SELECT                     ").append(" \n");
		sql.append(" 		 activityId,            ").append(" \n");
		sql.append(" 		 activityName,          ").append(" \n");
		sql.append(" 		 SUM(popUpNum) popUpNum,").append(" \n");
		sql.append(" 		 SUM(popUpPV) popUpPV,  ").append(" \n");
		sql.append(" 		 SUM(popUpUV) popUpUV   ").append(" \n");
		sql.append(" 	FROM (                      ").append(" \n");
		sql.append(" 	  SELECT                    ").append(" \n");
		sql.append(" 			IF(source_id > 0, source_id, c )  activityId,                                        ").append(" \n");
		sql.append(" 			IF((source_name IS NOT NULL AND source_name != '' ), source_name, b ) activityName,").append(" \n");
		sql.append(" 			if(dx_event_name = 'data_arrive',coalesce(count(*),0),0 ) popUpNum,                  ").append(" \n");
		sql.append(" 			if(dx_event_name = 'page_view',coalesce(count(*),0),0 ) popUpPV,                     ").append(" \n");
		sql.append(" 			if(dx_event_name = 'page_view',coalesce(count(DISTINCT roleid),0),0 ) popUpUV        ").append(" \n");
		sql.append(" 		FROM ").append(yunYingProperties.getHbdxtable() + " \n");
		sql.append(" 		where place in ('notice','float_tip')            ").append(" \n");

		sql.append(this.getCondition(req));

		sql.append(" 		and dx_event_name in ('data_arrive','page_view') ").append(" \n");
		sql.append(" 		and activityId != 0                              ").append(" \n");
		sql.append(" 		and activityName != ''                           ").append(" \n");
		sql.append(" 		and activityName != '<null>'                     ").append(" \n");
		sql.append(" 		group by activityId,activityName,dx_event_name   ").append(" \n");
		sql.append(" 	  ) s1                                                 ").append(" \n");
		sql.append(" 	  group by activityId,activityName                   ").append(" \n");
		sql.append(" 	  order by activityId desc                           ").append(" \n");
		sql.append("  ) b                                                    ").append(" \n");
		sql.append("  USING (activityId,activityName)                        ").append(" \n");
		sql.append("  ) ax                                                   ").append(" \n");
		sql.append("  FULL JOIN                                              ").append(" \n");
		sql.append("  (                                                      ").append(" \n");
		sql.append("   SELECT                                                ").append(" \n");
		sql.append("     activityId,                                        ").append(" \n");
		sql.append(" 	 activityName,                                       ").append(" \n");
		sql.append(" 	 SUM(oneWithdrawalClickNum)  oneWithdrawalClickNum,        ").append(" \n");
		sql.append(" 	 SUM(withdrawalRecordClickNum)  withdrawalRecordClickNum,                         ").append(" \n");
		sql.append(" 	 SUM(withdrawalClickNum)  withdrawalClickNum,        ").append(" \n");
		sql.append(" 	 SUM(earnClickNum)  earnClickNum,                                                 ").append(" \n");
		sql.append(" 	 SUM(floatClickNum)  floatClickNum                                                ").append(" \n");
		sql.append("  FROM                                                                                ").append(" \n");
		sql.append(" 	(                                                                                 ").append(" \n");
		sql.append(" 	SELECT                                                                            ").append(" \n");
		sql.append(" 	place,                                                                            ").append(" \n");
		sql.append(" 	IF(place = 'float_tip', IF(source_id > 0, source_id, c ), biz_id ) activityId,                                   ").append(" \n");
		sql.append(" 	IF(place = 'float_tip', IF( ( source_name IS NOT NULL AND source_name != '' ), source_name, b ), activity_name ) activityName,                          ").append(" \n");
		sql.append(" 	if(place = 'earnings',coalesce(count(*),0),0 ) earnClickNum,                      ").append(" \n");
		sql.append(" 	if(place = 'float_tip',coalesce(count(*),0),0 ) floatClickNum,                    ").append(" \n");
		sql.append(" 	if(place = 'withdrawal',coalesce(count(*),0),0 ) oneWithdrawalClickNum,              ").append(" \n");
		sql.append(" 	if(place = 'withdrawal_record',coalesce(count(*),0),0 ) withdrawalRecordClickNum,  ").append(" \n");
		sql.append(" 	if(place = 'withdrawal_click',coalesce(count(*),0),0 ) withdrawalClickNum  ").append(" \n");
		sql.append(" 	from ").append(yunYingProperties.getHbdxtable() +  " \n");
		sql.append(" 	where                                                                             ").append(" \n");
		sql.append(" 	dx_event_name = 'module_click'                                                    ").append(" \n");
		sql.append(" 	and place in('withdrawal','withdrawal_record','earnings','float_tip','withdrawal_click')             ").append(" \n");

		sql.append(this.getCondition(req));

		sql.append(" 	and activityId != 0                       ").append(" \n");
		sql.append(" 	and activityName != ''                    ").append(" \n");
		sql.append(" 	group by activityId,activityName,place    ").append(" \n");
		sql.append(" 	order by activityId desc                  ").append(" \n");
		sql.append("     ) s2                                      ").append(" \n");
		sql.append(" group by activityId,activityName             ").append(" \n");
		sql.append(" order by activityId desc                     ").append(" \n");
		sql.append("  ) bx                                        ").append(" \n");
		sql.append(" USING (activityId,activityName)              ").append(" \n");
		sql.append(" order by activityId desc                     ").append(" \n");
		log.info("sql : [{}]", sql.toString());
		List<HbActiveVisitDetailVo> list = clickhouseTemplate.query(sql.toString(), new Object[]{},
				new BeanPropertyRowMapper<>(HbActiveVisitDetailVo.class));
		return list;
	}
}
